use MockProject
go

create procedure OrgGetAll
as
	select o.OrgID, o.Name, a.Line1, a.PostCode, c.FirstName, c.Surname, o.IsActive
	from Organization o inner join Contact c on o.ContactID = c.ContactID 
		inner join Address a on o.AddressID = a.AddressID
go

/-- ----------------------------------/
/-- ----------------------------------/

create procedure OrgGetDetails2
(
	@OrgID uniqueidentifier
)
as
	select rf.RefID, rf.RefCode, rf.RefValue 
	from Organization o inner join OrgRef oref on o.OrgID = oref.OrgID inner join ReferenceData rf on oref.RefID = rf.RefID
	where o.OrgID = @OrgID

declare @a uniqueidentifier
set @a = newid()
exec OrgGetById @a
go

/-- ----------------------------------/
/-- ----------------------------------/

create procedure OrgGetById
(
	@OrgID uniqueidentifier
)
as
	select o.Name, o.ShortDescription, c.FirstName, c.Surname,
		a.Line1, a.Line2, a.Line3, a.PostCode, t.Name as Town, ct.Name as County, ctr.Name as Country, o.IsPreferred,
		r.RefValue, r.RefCode, o.FullDescription, o.PhoneNumber, o.Fax, o.WebAddress,
		o.CharityNumber, o.CompanyNumber, o.IsActive, o.Email
	from Organization o inner join Contact c on o.ContactID = c.ContactID 
		inner join Address a on o.AddressID = a.AddressID inner join Town t on a.TownID = t.TownID
		inner join Country ctr on a.CountryID = ctr.CountryID inner join County ct on a.CountyID = ct.CountyID
		inner join ReferenceData r on o.RefID = r.RefID
	where o.OrgID = @OrgID

	exec  OrgGetDetails2 @OrgID
go

/-- ----------------------------------/
/-- ----------------------------------/

create procedure OrgInsert
(
@Name nvarchar(50),
@ShortDescription nvarchar(255), 
@ContactID uniqueidentifier = null, 
@AddressID uniqueidentifier, 
@IsPreferred bit, 
@FullDescription ntext = '',
@PhoneNumber nvarchar(20),
@Fax nvarchar(20) = '',
@WebAddress nvarchar(50) = '',
@CharityNumber nvarchar(50) = '',
@CompanyNumber nvarchar(50) = '',
@IsActive bit,
@RefID uniqueidentifier,
@Email nvarchar(50)= ''	
)
as
	insert into 
		Organization(
			OrgID,
			Name, 
			ShortDescription,
			ContactID,
			AddressID,
			IsPreferred,
			FullDescription,
			PhoneNumber,
			Fax,
			WebAddress,
			CharityNumber,
			CompanyNumber,
			IsActive,
			RefID,
			Email)
	values(newid(), @Name, @ShortDescription, @ContactID, @AddressID, @IsPreferred, @FullDescription,@PhoneNumber,@Fax,@WebAddress,@CharityNumber,@CompanyNumber,@IsActive,@RefID,@Email)
go

/-- ----------------------------------/
/-- ----------------------------------/

create procedure OrgUpdate
(
@OrgID uniqueidentifier,
@Name nvarchar(50),
@ShortDescription nvarchar(255), 
@ContactID uniqueidentifier = null, 
@AddressID uniqueidentifier, 
@IsPreferred bit, 
@FullDescription ntext = '',
@PhoneNumber nvarchar(20),
@Fax nvarchar(20) = '',
@WebAddress nvarchar(50) = '',
@CharityNumber nvarchar(50) = '',
@CompanyNumber nvarchar(50) = '',
@IsActive bit,
@RefID uniqueidentifier,
@Email nvarchar(50)= ''	
)
as
update Organization
set
	[Name] = @Name, 
	ShortDescription = @ShortDescription,
	ContactID = @ContactID,
	AddressID = @AddressID,
	IsPreferred = @IsPreferred,
	FullDescription = @FullDescription,
	PhoneNumber = @PhoneNumber,
	Fax = @Fax,
	WebAddress = @WebAddress,
	CharityNumber = @CharityNumber,
	CompanyNumber = @CompanyNumber,
	IsActive = @IsActive,
	RefID = @RefID
where OrgID = @OrgID


/*$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ CHANH HERE $$$$$$$$$$$$$$$$$$$$$$$$$$*/]


/**************************************************Insert reference data*/

create proc InsertReference
(
@code nvarchar(50),
@value nvarchar(50)
)
as
insert into ReferenceData values(newID(),@code,@value)

exec InsertReference 'BestContactMethod','Email'

/**************************************************Insert Contact*/

create proc InsertContact
(
@first nvarchar(50),
@sur nvarchar(50),
@Known nvarchar(50),
@OP nvarchar(20),
@MP nvarchar(20),
@SP nvarchar(20),
@Email nvarchar(50),
@Manage uniqueidentifier,
@ContactType uniqueidentifier,
@Best uniqueidentifier,
@Role nvarchar(50),
@Work nvarchar(50),
@Titel nvarchar(50),
@Active bit
)
as
exec Insertcontact 'Dung','Thai','Minh Dung','099999999','09999999','08888888','Dungtm3@fsoft.com.vn',
'0c8a5b8a-0fc7-4181-b611-206ae98f1c51','94465ea5-845b-4df6-b64b-5deb6ea7d9f5','43f7d221-2ba2-4a67-b7cc-80ac87958de2',
'Employee','Office','Listen phone',true


/*******************************************************Insert Address*/

ALTER proc [dbo].[InsertAddress](
@Line1 nvarchar(50),
@Line2 nvarchar(50),
@Line3 nvarchar(50),
@Postcode nvarchar(50),
@IDTown uniqueidentifier,
@IDCountry uniqueidentifier,
@IDCounty uniqueidentifier
)
as
insert into [Address] values(newID(),@Line1,@Line2,@Line3,@Postcode,@IDTown,@IDCountry,@IDCounty)

/************************************************Insert Country***************/
ALTER proc [dbo].[InsertCountry](
@NAME nvarchar(50) 
)
as
insert into Country values(newID(),@NAME)

/************************************************Insert County******************/

ALTER proc [dbo].[InsertCounty](
@ID uniqueidentifier,
@NAME nvarchar(50) 
)
as
insert into County values(newID(),@ID,@NAME)

/************************************InsertTown*/
ALTER proc [dbo].[InsertTown](
@IDCountry uniqueidentifier,
@IDCounty uniqueidentifier,
@NAME nvarchar(50) 
)
as
insert into Town values(newID(),@IDCountry,@IDCounty,@NAME)


/***********************************************Trust Region**/
create proc InsertTrustRegion
(
@name nvarchar(50),
@Des ntext,
@Country uniqueidentifier
)
as
insert into TrustRegion values(newID(),@name,@des,@country)

exec InsertTrustRegion 'South-East Asia','Include: Viet Nam, Lao, Campuchia,...',
'ac084881-12b2-4d30-aa62-fbf8545007b5'

exec InsertTrustRegion 'South-East Asia','Include: Viet Nam, Lao, Campuchia,...',
'820805e9-77bb-4f1e-bf97-c71681f76d61'

/***************************************TrustDistrict*/

create proc InsertTrustDistrict
(
@name nvarchar(50),
@Des ntext,
@TrustID uniqueidentifier
)
as
insert into TrustDistrict values(newID(),@name,@des,@TrustID)

exec InsertTrustDistrict 'East','Include: Campuchia',
'8d0af889-1dfd-4419-8563-3d39184d7191'

/*********************************************Insert Organization*/

create proc InsertOrganization
(
@name nvarchar(50),
@ShortDes nvarchar(255),
@ContactID uniqueidentifier,
@AddressID uniqueidentifier,
@Ispre bit,
@fulldes ntext,
@Phone nvarchar(20),
@fax nvarchar(20),
@Web nvarchar(50),
@char nvarchar(50),
@comp nvarchar(50),
@IsAc bit,
@ref uniqueidentifier
)
as
insert into Organization values(newID(),@name,@ShortDes,@ContactID,@AddressID,@Ispre,@fulldes,@Phone,
@fax,@Web,@char,@comp,@IsAc,@ref)

exec InsertOrganization 'IT services','supports it','0c8a5b8a-0fc7-4181-b611-206ae98f1c51',
'ce207568-8900-484c-9c6e-335f896c6550',true,'','080909090','090808907','it.support.com','09786786','0897687',
true,'0203c7a3-492e-4941-bfd3-b6672c8045e8'

exec InsertOrganization 'Asset services','supports manager','0c8a5b8a-0fc7-4181-b611-206ae98f1c51',
'ce207568-8900-484c-9c6e-335f896c6550',true,'','080909090','090808907','Asset.support.com','09786786','0897687',
true,'0203c7a3-492e-4941-bfd3-b6672c8045e8'

/**********************************************Insert Programme*/
create proc InsertProgramme
(
@name nvarchar(50),
@Des ntext,
@ContactID uniqueidentifier
)
as
insert into Programme values(newID(),@name,@des,@ContactID)

exec InsertProgramme 'Programme1','Execute programmme1','0c8a5b8a-0fc7-4181-b611-206ae98f1c51'
exec InsertProgramme 'Programme4','Execute programmme4','0c8a5b8a-0fc7-4181-b611-206ae98f1c51'
exec InsertProgramme 'Programme2','Execute programmme2','0c8a5b8a-0fc7-4181-b611-206ae98f1c51'
exec InsertProgramme 'Programme3','Execute programmme3','0c8a5b8a-0fc7-4181-b611-206ae98f1c51'
